package cn.com.utt.unms.mapper;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserCountMapper {
	
	@Select({"<script>",
	    "select ssid as type,count(*) as total from user_record ", 
	    "WHERE ", 
	    "sn IN", 
	      "<foreach item='item' index='index' collection='sns'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and status=1 group by `ssid` ",
	"</script>"})
	List<Map<String,Object>> getPieBySsid(@Param("sns")List<String> sns);
	
	@Select({"<script>",
	    "select band as type,count(*) as total from user_record ", 
	    "WHERE ", 
	    "sn IN", 
	      "<foreach item='item' index='index' collection='sns'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and status=1 group by `band` ",
	"</script>"})
	List<Map<String,Object>> getPieByAccessType(@Param("sns")List<String> sns);
	
	@Select({"<script>",
	    "select os as type,count(*) as total from user_record ", 
	    "WHERE ", 
	    "sn IN", 
	      "<foreach item='item' index='index' collection='sns'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and status=1 group by `os` ",
	"</script>"})
	List<Map<String,Object>> getPieByOS(@Param("sns")List<String> sns);
	
	@Select({"<script>",
	    "select a.time,IFNULL(b.g24,0) as g24,IFNULL(c.g5,0) as g5,IFNULL(d.total,0) as total from ", 
	    "(select DATE_FORMAT(start_time,#{range}) as time from user_record where device_id in", 
	      "<foreach item='item' index='index' collection='ids'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and start_time > #{startTime} and start_time &lt; #{endTime} group by DATE_FORMAT(start_time,#{range})) a left join ",
	      "(select count(*) as g24,DATE_FORMAT(start_time,#{range}) as time from user_record where device_id in", 
	      "<foreach item='item' index='index' collection='ids'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and start_time > #{startTime} and start_time &lt; #{endTime} and ssid_type=1 group by DATE_FORMAT(start_time,#{range})) b on a.time=b.time left join ",
	      "(select count(*) as g5,DATE_FORMAT(start_time,#{range}) as time from user_record where device_id in", 
	      "<foreach item='item' index='index' collection='ids'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and start_time > #{startTime} and start_time &lt; #{endTime} and ssid_type=2 group by DATE_FORMAT(start_time,#{range})) c on a.time=c.time left join ",
	      "(select count(*) as total,DATE_FORMAT(start_time,#{range}) as time from user_record where device_id in", 
	      "<foreach item='item' index='index' collection='ids'",
	        "open='(' separator=',' close=')'>",
	        "#{item}",
	      "</foreach>",
	      " and start_time > #{startTime} and start_time &lt; #{endTime} group by DATE_FORMAT(start_time,#{range})) d on a.time=d.time",
	"</script>"})
	List<Map<String,Object>> getLineByTime(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("range")String range,@Param("ids")List<Long> ids);
	
}
